﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using KCL.Code.Entities;
using System.Data;

namespace KCL.Code.Data
{
    public class UserDAO : BaseDAO
    {
        public List<User> GetUsers(int maximumRows, int startRowIndex, string sortExpression)
        {
            SqlConnection myConnection = this.GetConnection();
            string sqlStatement = string.Format("WITH OrderedUser AS (SELECT *, ROW_NUMBER() OVER (ORDER BY {0}) AS 'RowNumber' FROM [User] WHERE isActive=1) SELECT * FROM OrderedUser WHERE RowNumber BETWEEN {1} AND {2}", sortExpression != string.Empty ? sortExpression : "id", startRowIndex, startRowIndex + maximumRows);
            SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
            SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
            myConnection.Open();
            DataTable dt = new DataTable();
            myAdapter.Fill(dt);
            List<User> results = new List<User>();
            int currentIndex = 0;
            while (currentIndex < dt.Rows.Count)
            {
                User User = new User();
                User.id = Convert.ToInt32(dt.Rows[currentIndex]["id"]);
                User.username = dt.Rows[currentIndex]["username"].ToString();
                User.password = dt.Rows[currentIndex]["password"].ToString();
                User.fullname = dt.Rows[currentIndex]["fullname"].ToString();
                User.email = dt.Rows[currentIndex]["email"].ToString();
                User.code = dt.Rows[currentIndex]["code"].ToString();
                User.admin = Convert.ToBoolean(dt.Rows[currentIndex]["admin"]);
                User.read = Convert.ToBoolean(dt.Rows[currentIndex]["read"]);
                User.create = Convert.ToBoolean(dt.Rows[currentIndex]["create"]);
                User.edit = Convert.ToBoolean(dt.Rows[currentIndex]["edit"]);
                User.isActive = Convert.ToBoolean(dt.Rows[currentIndex]["isActive"]);
                results.Add(User);
                currentIndex++;
            }
            myConnection.Close();
            return results;
        }

        public int GetTotalUser()
        {
            SqlConnection myConnection = this.GetConnection();
            string sqlStatement = "SELECT COUNT(*) FROM [User] WHERE isActive=1";
            SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
            myConnection.Open();
            int totalUser = Convert.ToInt32(myCommand.ExecuteScalar());
            myConnection.Close();
            return totalUser;
        }

        public string CreateUser(string username, string password, string fullname, string email, string admin, string create, string edit)
        {
            SqlConnection myConnection = this.GetConnection();
            string code = "xXx";
            string sqlStatement = string.Format("INSERT INTO [User]([username], [password], [fullname], [email], [code], [admin], [create], [edit]) VALUES(N'{0}', N'{1}', N'{2}', N'{3}', N'{4}', {5}, {6}, {7})", username, password, fullname, email, code, admin, create, edit);
            sqlStatement = sqlStatement.Replace("N''", "NULL");
            SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.CommandText = "SELECT @@IDENTITY";
                object orderId = myCommand.ExecuteScalar();
                code = "U" + orderId.ToString();
                myCommand.CommandText = string.Format("UPDATE [User] SET [code]=N'{0}' WHERE [id]={1}", code, orderId);
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return code;
        }

        public bool UpdateUser(string id, string username, string password, string fullname, string email, string admin, string create, string edit)
        {
            SqlConnection myConnection = this.GetConnection();
            string sqlStatement = string.Format("UPDATE [User] SET username=N'{0}', password=N'{1}', fullname=N'{2}', email=N'{3}', admin={4}, create={6}, edit={7} WHERE id={11}", username, password, fullname, email, admin, create, edit, id);
            sqlStatement = sqlStatement.Replace("N''", "NULL");
            SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        public bool DeleteUserByID(int id)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("UPDATE [User] SET isActive=0 WHERE id={0}", id);
                SqlCommand myCommand = new SqlCommand(sqlStatement, connection);

                try
                {
                    connection.Open();
                    return (myCommand.ExecuteNonQuery() == 1);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public DataTable CheckUserLogin(string username, string password)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT [id], [fullname], [admin], [read], [create], [edit] FROM [User] WHERE [isActive] = 1 AND [username] = N'{0}' AND [password] = N'{1}'", username, password);
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                command.CommandType = CommandType.Text;

                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                DataTable userInfo = new DataTable();

                connection.Open();
                sqlAdapter.Fill(userInfo);
                return userInfo;
            }
        }

        public bool ChangePassword(string userid, string newpassword)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format(string.Format("UPDATE [KCL].[dbo].[User] SET [password] = N'{0}' WHERE [id] = {1}", newpassword, userid));
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                command.CommandType = CommandType.Text;
                try
                {
                    connection.Open();
                    return (command.ExecuteNonQuery()==1);
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }
    }
}